IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[CGO_EventGetList]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
  DROP PROCEDURE CGO_EventGetList
GO
  

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO


/** Create Stored Procedures **/


CREATE PROCEDURE dbo.CGO_EventGetList
  @PortalID INT,
  @CharityID INT,
  @DateBegin DATETIME,
  @DateEnd DATETIME,
  @GetCount BIT = 0,
  @PageSize INT = 10,
  @CurrentPage INT = 1
AS 
  DECLARE @TotalRowCount INT
  SET @TotalRowCount = 0
  
  IF @GetCount IS NULL
  BEGIN
  	SET @GetCount = 0
  END
  IF @PageSize IS NULL
  BEGIN
	  SET @PageSize = 10
  END
  IF @CurrentPage IS NULL
  BEGIN
	  SET @CurrentPage = 1
  END
  
  IF @CharityID IS NULL
  BEGIN
  	SET @CharityID = 0
  END
  
  IF @DateBegin IS NOT NULL
  BEGIN
  	SET @DateBegin = CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateBegin,101),101)
  END
  
  IF @DateEnd  IS NOT NULL
  BEGIN
  	SET @DateEnd = CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateEnd,101),101)
  END
  
  IF @DateBegin IS NOT NULL AND @DateEnd IS NOT NULL
  BEGIN
  	IF @DateBegin=@DateEnd
  	BEGIN
  		SET @DateEnd = DATEADD(DAY,1,@DateEnd)	
  	END
  END
  /*
  IF @DateBegin IS NOT NULL AND @DateEnd IS NOT NULL AND @DateBegin<=@DateEnd
  BEGIN
  	SET @DateBegin = CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateBegin,101),101)
  	IF @DateBegin=@DateEnd
  	BEGIN
  	  SET @DateEnd = DATEADD(DAY,1,CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateEnd,101),101))	
  	END
  	ELSE
  	BEGIN
  		SET @DateEnd = CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateEnd,101),101)
  	END  	
  END
  ELSE
  BEGIN
  	SET @DateBegin = NULL
  	SET @DateEnd = NULL
  END
  */
  
  IF @GetCount = 1
  BEGIN
    SELECT  @TotalRowCount = COUNT(*)
    FROM    dbo.CGO_Event
    WHERE   PortalID = @PortalID          
            AND ( @CharityID = 0
                  OR EventID IN ( SELECT  EventID
                                  FROM    dbo.CGO_EventCharity
                                  WHERE   CharityID = @CharityID
                                          AND PortalID = @PortalID )
                )
            AND ( ( @DateBegin IS NULL AND @DateEnd IS NULL)
                  OR ( @DateBegin IS NOT NULL AND @DateEnd IS NULL AND DateEnd>=@DateBegin)
                  OR ( @DateBegin IS NULL AND @DateEnd IS NOT NULL AND DateEnd<=@DateEnd )
                  OR ( @DateBegin IS NOT NULL AND @DateEnd IS NOT NULL AND DateBegin <= @DateEnd AND DateEnd >= @DateBegin)
                )
  END
  
  SET ROWCOUNT @PageSize
  
  SELECT  @TotalRowCount AS TotalRowCount ,
          T.*
  FROM    ( SELECT  ROW_NUMBER() OVER ( ORDER BY DateBegin ASC, Name ASC, LastUpdateDate DESC) AS RANK ,
                    EventID ,
                    PortalID ,
                    DateBegin ,
                    DateEnd ,
                    Name ,
                    Description ,
                    MaxEnrollment ,
                    EnrollFee ,
                    EnrollType ,
                    ImageURL ,
                    ImageType ,
                    ImageWidth ,
                    ImageHeight ,
                    Status ,
                    Category ,
                    ImageDisplay ,
                    Signups ,
                    AllDayEvent ,
                    Approved ,
                    CreatedByID ,
                    UC.Username AS CreateByUserName ,
                    CreatedDate ,
                    LastUpdatedID ,
                    UU.Username AS LastUpdateUserName ,
                    LastUpdateDate
            FROM    dbo.CGO_Event M
                    LEFT JOIN dbo.Users UC ON M.CreatedByID = UC.UserID
                    LEFT JOIN dbo.Users UU ON M.LastUpdatedID = UU.UserID
            WHERE   PortalID = @PortalID
                    AND ( @CharityID = 0
                          OR EventID IN ( SELECT  EventID
                                          FROM    dbo.CGO_EventCharity
                                          WHERE   CharityID = @CharityID
                                                  AND PortalID = @PortalID )
                        )
                    AND ( ( @DateBegin IS NULL AND @DateEnd IS NULL)
                          OR ( @DateBegin IS NOT NULL AND @DateEnd IS NULL AND DateEnd>=@DateBegin)
                          OR ( @DateBegin IS NULL AND @DateEnd IS NOT NULL AND DateEnd<=@DateEnd )
                          OR ( @DateBegin IS NOT NULL AND @DateEnd IS NOT NULL AND DateBegin <= @DateEnd AND DateEnd >= @DateBegin)
                        )
          ) T
  WHERE   T.[RANK] > ( @CurrentPage - 1 ) * @PageSize
GO
